package com.systemdevelopment.mainproject.Model;

import java.util.HashSet;
import java.sql.*;
import java.util.Objects;

public class Author {//作者类
    protected String Name;      //姓名
    protected String Code;      //编号
    protected String Nation;    //国籍

    public static void main(String[] args) {        //测试代码

        HashSet<Author> authorSet = ReadAuthorFromDBByName("cat");  //测试1
        for (Author au:authorSet){
            System.out.println(au.getCode());
        }

        /*Author author = ReadAuthorFromDBByCode("94627289");   //测试2
        System.out.println(author.getCode());*/

        /*HashSet<Author> authorSet = new HashSet<>();      //测试3
        Author author1 = new Author("90735791","puppy","JAPAN");
        Author author2 = new Author("23647825","pirate","UK");
        authorSet.add(author1);
        authorSet.add(author2);

        InsertAuthorIntoDB(authorSet);*/
    }

    public Author(){}

    public Author(String code,String name,String nation){
        this.Code = code;
        this.Name = name;
        this.Nation = nation;
    }

    public String getName() {
        return Name;
    }

    public String getCode() {
        return Code;
    }

    public String getNation() {
        return Nation;
    }

    public void setName(String name) {
        Name = name;
    }

    public void setCode(String code) {
        Code = code;
    }

    public void setNation(String nation) {
        Nation = nation;
    }

    @Override
    public boolean equals(Object o) {
        if (this == o) return true;
        if (o == null || getClass() != o.getClass()) return false;
        Author author = (Author) o;
        return Objects.equals(Name, author.Name) &&
                Objects.equals(Code, author.Code) &&
                Objects.equals(Nation, author.Nation);
    }

    @Override
    public int hashCode() {
        return Objects.hash(Name, Code, Nation);
    }


    public static HashSet<Author> ReadAllAuthorsFromDB() {//从数据库的AUTHOR表中检索所有作者，存储在HashSet<Author>并将其作为返回值
        HashSet<Author> AuthorSet = new HashSet<>();
        try (Connection conn = DataBaseManager.build_conn()) {

            String Sql = "select * from LIB.AUTHOR";

            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(Sql);

            while (rs.next()) {
                String code = rs.getString("CODE");
                String name = rs.getString("NAME");
                String nation = rs.getString("NATION");

                Author author = new Author();
                if (code != null && code.length() > 0) {
                    author.setCode(code);
                    author.setName(name);
                    author.setNation(nation);
                }
                AuthorSet.add(author);
            }
        } catch (SQLException Se) {
            Se.printStackTrace();
        }

        return AuthorSet;
    }

    @Override
    public String toString() {
        return
                "姓名:" + Name +
                ", 作者编号:" + Code +
                ", 国籍:" + Nation;
    }

    public static HashSet<Author> ReadAuthorFromDBByName(String Name) {//根据姓名从数据库的AUTHOR表中检索作者，结果可能有多个，存储在HashSet<Author>并将其作为返回值
        HashSet<Author> AuthorSet = new HashSet<>();
        try (Connection conn = DataBaseManager.build_conn()) {

            String Sql = "select * from LIB.AUTHOR where NAME = " + "'" + Name + "'";

            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(Sql);

            while (rs.next()) {
                String code = rs.getString("CODE");
                String name = rs.getString("NAME");
                String nation = rs.getString("NATION");

                Author author = new Author();
                if (code != null && code.length() > 0) {
                    author.setCode(code);
                    author.setName(name);
                    author.setNation(nation);
                }
                AuthorSet.add(author);
            }
        } catch (SQLException Se) {
            Se.printStackTrace();
        }

        return AuthorSet;
    }

    public static Author ReadAuthorFromDBByCode(String Code) throws SQLException {//根据姓名和编号在数据库的AUTHOR表中检索作者，结果只能有一个，将唯一的Author对象作为返回值返回
        Connection conn = null;
        Author author = new Author();
        try {
            conn = DataBaseManager.build_conn();

            String Sql = "select * from LIB.AUTHOR where CODE =" + "'" + Code + "'";

            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(Sql);

            while (rs.next()) {
                String code = rs.getString("CODE");
                String name = rs.getString("NAME");
                String nation = rs.getString("NATION");

                if (code != null && code.length() > 0) {
                    author.setCode(code);
                    author.setName(name);
                    author.setNation(nation);
                }
            }
        }catch (SQLException Se) {
            Se.printStackTrace();
        }finally {
            assert conn != null;
            conn.close();
        }

        return author;
    }

    public static void InsertAuthorIntoDB(HashSet<Author> AuthorSet) throws SQLException {   //向数据库的AUTHOR表中插入多行，待插入的Author存储在HashSetM<Author>中
        Connection conn = null;
        try{
            conn = DataBaseManager.build_conn();
            Statement stmt = conn.createStatement();
            conn.setAutoCommit(false);


            HashSet<Author> AuthorsDB = Author.ReadAllAuthorsFromDB();
            for (Author au:AuthorSet){
                if(!AuthorsDB.contains(au)) {
                    String code = au.getCode();
                    String name = au.getName();
                    String nation = au.getNation();


                    String Sql = "Insert into LIB.AUTHOR VALUES(" + "'" + code + "'" + "," + "'" + name + "'" + "," + "'" + nation + "'" + ")";

                    stmt.execute(Sql);
                }
            }

            //int[] num = stmt.executeBatch();
            conn.commit();
        }catch (SQLException Se){
            Se.printStackTrace();
            assert conn != null;
            conn.rollback();
        }finally {
            if(conn!=null){
                conn.setAutoCommit(true); // 还原事务的提交方式
                conn.close();
            }
        }
    }

}
